package cn.jbolt.core.db.util;

import cn.jbolt.core.base.JBoltIDGenMode;
import cn.jbolt.core.base.config.JBoltConfig;
import cn.jbolt.core.db.datasource.JBoltDataSourceUtil;
import cn.jbolt.core.db.datasource.JBoltDatasource;
import cn.jbolt.core.db.sql.DBType;
import cn.jbolt.core.kit.JBoltSaasTenantKit;
import cn.jbolt.core.model.base.JBoltModelConfig;
import com.jfinal.kit.StrKit;
import com.jfinal.log.Log;
import com.jfinal.plugin.activerecord.DbKit;
import com.jfinal.plugin.activerecord.Model;

import java.sql.*;
import java.util.Set;

public class JBoltDbKit {
	/**
	 * 执行指定sql
	 * @param dataSourceConfigName
	 * @param sql
	 * @return
	 */
	public static boolean jdbcExeSql(String dataSourceConfigName,String sql) {
		return jdbcExeSql(dataSourceConfigName,sql,true);
	}
	/**
	 * 执行指定sql
	 * @param dataSourceConfigName
	 * @param sql
	 * @param closeConnection
	 * @return
	 */
	public static boolean jdbcExeSql(String dataSourceConfigName,String sql,boolean closeConnection) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = DbKit.getConfig(dataSourceConfigName).getConnection();
			if (connection == null) {
				return false;
			}
			statement = connection.createStatement();
			statement.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null && closeConnection) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return true;
	}
	/**
	 * 检测表是否存在
	 * @param datasourceName
	 * @param tableName
	 * @return
	 */
	public static boolean tableExist(String datasourceName,String tableName) {
		return tableExist(datasourceName,tableName,true);
	}
	/**
	 * 检测表是否存在
	 * @param datasourceName
	 * @param tableName
	 * @param closeConnection
	 * @return
	 */
	public static boolean tableExist(String datasourceName,String tableName,boolean closeConnection) {
		return tableExist(JBoltDataSourceUtil.me.getJBoltDatasource(datasourceName),tableName,closeConnection);
	}
	/**
	 * 检测表是否存在
	 * @param datasource
	 * @param tableName
	 * @return
	 */
	public static boolean tableExist(JBoltDatasource datasource,String tableName) {
		return tableExist(datasource,tableName,true);
	}
	/**
	 * 检测表是否存在
	 * @param datasource
	 * @param tableName
	 * @param closeConnection
	 * @return
	 */
	public static boolean tableExist(JBoltDatasource datasource,String tableName,boolean closeConnection) {
		if(datasource == null){
			throw new RuntimeException("tableExist: datasource is null");
		}
		if(StrKit.isBlank(tableName)){
			throw new RuntimeException("tableExist: tableName is null datasource:"+datasource.getName());
		}
		if (DBType.isOracle(datasource.getDbType())) {
			tableName = tableName.toUpperCase();
		}
		String schema = null;
		if(tableName.contains(".")) {
			String[] arr = null;
			if(DBType.isDM(datasource.getDbType()) && tableName.contains("\".\"")){
				arr = tableName.split("\"\\.\"");
			}else{
				arr = tableName.split("\\.");
			}
			schema = arr[0];
			if(schema.contains("\"")){
				schema = schema.replace("\"","");
			}
			tableName = arr[1];
			if(tableName.contains("\"")){
				tableName = tableName.replace("\"","");
			}
		}else{
			if(DBType.isMysql(datasource.getDbType())){
				schema = datasource.getDbName();
			}else{
				schema = datasource.getDbSchema();
			}

		}
		boolean flag = false;
		Connection connection = null;
		try {
			connection = DbKit.getConfig(datasource.getName()).getConnection();
			DatabaseMetaData meta = connection.getMetaData();
			String[] type = { "TABLE" };
			ResultSet rs = meta.getTables(connection.getCatalog(), schema, tableName, type);
			flag = rs.next();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (connection != null && closeConnection) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return flag;
	}

	/**
	 * 获取当前租户 指定model的分表
	 * @param fromTableModelClass
	 * @return
	 */
	public static String getCurrentTenantTable(Class<? extends Model<?>> fromTableModelClass) {
		return getTenantTable(fromTableModelClass, JBoltSaasTenantKit.me.getIdToStr());
	}
	/**
	 * 获取指定租户 指定model的分表 名称
	 * @param fromTableModelClass
	 * @param tenantId
	 * @return
	 */
	public static String getTenantTable(Class<? extends Model<?>> fromTableModelClass,String tenantId) {
		if(fromTableModelClass == null) {
			throw new RuntimeException("未指定模板表对应ModelClass");
		}
		if(StrKit.isBlank(tenantId)) {
			throw new RuntimeException("无法获取当前租户信息");
		}
		String fromTableName = JBoltModelConfig.me.getTableName(fromTableModelClass);
		if(StrKit.isBlank(fromTableName)) {
			throw new RuntimeException("模板["+fromTableModelClass.getName()+"]没有加入TableMapping");
		}
		return fromTableName + "_" + tenantId;
	}

	/**
	 * 创建id自增序列
	 *
	 * @param datasourceConfigName
	 * @param tableName
	 * @return
	 */
	public static boolean createIdSequence(String datasourceConfigName,String tableName) {
		if(StrKit.hasBlank(datasourceConfigName,tableName)) {
			throw new RuntimeException("所有参数必须填写");
		}
		JBoltDatasource toJboltDatasource = JBoltDataSourceUtil.me.getJBoltDatasource(datasourceConfigName);
		if(toJboltDatasource == null) {
			throw new RuntimeException("指定的数据源["+datasourceConfigName+"]不存在 请检查配置");
		}
		String dbType = toJboltDatasource.getDbType();
		String sql = null;
		switch (dbType) {
		case DBType.ORACLE:
			sql = "create sequence %s_idsq" + " minvalue 10000" + " maxvalue 9999999999999999999999999999"
					+ " start with 10001" + " increment by 1" + " cache 20";
			break;
		case DBType.POSTGRESQL:
			sql = "create sequence %s_idsq" + " minvalue 10000" + " maxvalue 9999999999999999999999999999"
					+ " start with 10001" + " increment by 1" + " cache 20";
			break;
		case DBType.DM:
			sql = "create sequence %s_idsq" + " minvalue 10000" + " maxvalue 9999999999999999999999999999"
					+ " start with 10001" + " increment by 1" + " cache 20";
			break;
		}
		assert sql != null;
		return jdbcExeSql(datasourceConfigName,String.format(sql, tableName));
	}

	/**
	 * 创建指定租户指定数据源的分表
	 * 来自于saas config配置
	 * @param fromJboltDatasource
	 * @param toJboltDatasource
	 * @param tenantId
	 * @return
	 */
	public static boolean createProjectTenantSeparateTableFromSaasConfig(JBoltDatasource fromJboltDatasource,JBoltDatasource toJboltDatasource, String tenantId) {
		Set<Class<? extends Model>> modelSet  = JBoltModelConfig.me.getProjectSaasConfigSeparateModelSet();
		if(modelSet == null) {
			throw new RuntimeException("saas config 中未配置可用租户分表序列");
		}
		modelSet.forEach(fromTableModelClass->createTenantSeparateTable(fromJboltDatasource, toJboltDatasource, fromTableModelClass, tenantId));
		return true;
	}
	/**
	 * 创建指定租户指定数据源的指定表的分表
	 * @param fromJboltDatasource
	 * @param toJboltDatasource
	 * @param fromTableModelClass
	 * @param tenantId
	 * @return
	 */
	public static boolean createTenantSeparateTable(JBoltDatasource fromJboltDatasource,JBoltDatasource toJboltDatasource, Class<? extends Model> fromTableModelClass, String tenantId) {
		if(fromJboltDatasource == null || toJboltDatasource == null || fromTableModelClass == null || StrKit.isBlank(tenantId)) {
			throw new RuntimeException("所有参数必须填写");
		}
		String fromTableName = JBoltModelConfig.me.getTableName(fromTableModelClass);
		if(StrKit.isBlank(fromTableName)) {
			throw new RuntimeException("模板["+fromTableModelClass.getName()+"]没有加入TableMapping");
		}
		String newTableName = fromTableName + "_" + tenantId;
		String idGenMode =JBoltModelConfig.me.getIdGenMode(fromTableModelClass);
		return createSeparateTable(fromJboltDatasource.getDbName(), fromTableName,toJboltDatasource,newTableName,idGenMode);
	}
	/**
	 * 创建租户分表
	 * @param fromTableModelClass
	 * @param toDatasourceConfigName
	 * @param tenantId
	 * @return
	 */
	public static boolean createTenantSeparateTable(Class<? extends Model> fromTableModelClass,String toDatasourceConfigName,String tenantId) {
		if(fromTableModelClass == null) {
			throw new RuntimeException("未指定来源表的modelClass");
		}
		if(StrKit.isBlank(toDatasourceConfigName)) {
			throw new RuntimeException("未指定生成到哪个数据源");
		}
		if(StrKit.isBlank(tenantId)) {
			throw new RuntimeException("未指定租户ID");
		}
		String fromTableName = JBoltModelConfig.me.getTableName(fromTableModelClass);
		if(StrKit.isBlank(fromTableName)) {
			throw new RuntimeException("模板["+fromTableModelClass.getName()+"]没有加入TableMapping");
		}
		String fromDatasourceConfigName = JBoltModelConfig.me.getDataSourceConfigName(fromTableModelClass);
		String newTableName = fromTableName + "_" + tenantId;
		String idGenMode =JBoltModelConfig.me.getIdGenMode(fromTableModelClass);
		return createSeparateTable(fromDatasourceConfigName,fromTableName,toDatasourceConfigName,newTableName,idGenMode);
	}


	/**
	 * 创建分表到另一个数据源里
	 * @param fromDatasourceConfigName
	 * @param fromTableName
	 * @param toDatasourceConfigName
	 * @param newTableName
	 * @param idGenMode
	 * @return
	 */
	public static boolean createSeparateTable(String fromDatasourceConfigName,String fromTableName,String toDatasourceConfigName,String newTableName,String idGenMode) {
		JBoltDatasource fromJboltDatasource = JBoltDataSourceUtil.me.getJBoltDatasource(fromDatasourceConfigName);
		JBoltDatasource toJboltDatasource = JBoltDataSourceUtil.me.getJBoltDatasource(toDatasourceConfigName);
		return createSeparateTable(fromJboltDatasource.getDbName(),fromTableName,toJboltDatasource,newTableName,idGenMode);
	}

	/**
	 * 创建分表
	 * @param fromDatabaseName
	 * @param fromTableName
	 * @param toJboltDatasource
	 * @param newTableName
	 * @param idGenMode
	 * @return
	 */
	public static boolean createSeparateTable(String fromDatabaseName,String fromTableName,JBoltDatasource toJboltDatasource,String newTableName,String idGenMode) {
		if(toJboltDatasource==null || StrKit.hasBlank(newTableName,fromDatabaseName,fromTableName)) {
			throw new RuntimeException("所有参数必须填写");
		}
		boolean exist = JBoltDbKit.tableExist(toJboltDatasource,newTableName);
		if(exist) {return true;}

		String sql = null;
		String dbType = toJboltDatasource.getDbType();
		switch (dbType) {
		case DBType.MYSQL:
			sql = "create table " + toJboltDatasource.getDbName()+"."+newTableName + " LIKE " + fromDatabaseName+"."+fromTableName;
			break;
		case DBType.DM:
			String dmSchema = StrKit.defaultIfBlank(toJboltDatasource.getDbName(),toJboltDatasource.getDbSchema());
			if(dmSchema!=null && dmSchema.trim().length()>0){
				if(!newTableName.contains(".")){
					newTableName  = "\""+dmSchema+"\""+"."+"\""+newTableName+"\"";
				}else{
					newTableName  = "\""+newTableName+"\"";
				}
			}else{
				newTableName  = "\""+newTableName+"\"";
			}
			fromDatabaseName = "\""+fromDatabaseName+"\"";
			fromTableName = "\""+fromTableName+"\"";
			sql = "create table " + newTableName + " LIKE " + fromDatabaseName+"."+fromTableName;
			break;
		case DBType.POSTGRESQL:
			sql = "create table " + toJboltDatasource.getDbName()+"." + newTableName + " (LIKE " + fromDatabaseName+"."+fromTableName + ")";
			break;
		case DBType.SQLSERVER:
			String schemaSetting = toJboltDatasource.getDbSchema();
			String schema = "dbo";
			if(StrKit.notBlank(schemaSetting) && !schemaSetting.equals(schema)){
				schema = schemaSetting;
			}
			if(!newTableName.contains(".")){
				newTableName  = schema+"."+newTableName;
			}
			if(!fromTableName.contains(".")){
				fromTableName  = schema+"."+fromTableName;
			}
			sql = "select  * into " + toJboltDatasource.getDbName()+"." + newTableName + " from " + fromDatabaseName+"."+fromTableName;
			break;
		case DBType.ORACLE:
			sql = "create table " + "." + newTableName + " as select * from " + fromTableName + " where 1=2";
			break;
		}
		boolean success = JBoltDbKit.jdbcExeSql(toJboltDatasource.getName(),sql);
		if (!success) {
			return false;
		}
		exist = tableExist(toJboltDatasource,newTableName);
		if(exist) {
			if(StrKit.notBlank(idGenMode) && (idGenMode.equals(JBoltIDGenMode.SEQUENCE) || idGenMode.equals(JBoltIDGenMode.SEQUENCE_LONG))) {
				// 如果判断ID使用序列自增就需要动态创建序列了
				if (JBoltConfig.isOracle() || JBoltConfig.isDM() || JBoltConfig.isPostgresql()) {
					createIdSequence(toJboltDatasource.getName(), newTableName);
				}
			}

			Log.getLog(JBoltDbKit.class).debug("创建分表"+toJboltDatasource.getDbName()+"."+newTableName+"成功!");
		}
		return exist;
	}



}
